package com.ce.pms.charge.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.charge.dao.IAccountDao;
import com.ce.pms.charge.model.AccountDomain;
import com.ce.pms.charge.vo.PrePayOffsetSearchVO;
import com.ce.pms.charge.vo.PrePayOffsetVO;
import com.ce.pms.common.annotation.AssociatedBuildingQuery;
import com.ce.pms.common.util.SqlConfig;
import com.ce.util.BlankUtil;
import com.sitechasia.webx.core.support.Page;

public class AccountDaoImpl extends PmsBaseDaoImpl<AccountDomain> implements IAccountDao {

	/**
	 * 功能描述: 得到指定产权和房屋的账户，
	 * 参数isActive如果为true，则只查询其有效账户，否则全都查询 <br>
	 * @param proRightId
	 * @param houseId
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-8-26  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	@SuppressWarnings("unchecked")
	public AccountDomain getAccountBy(Long proRightId,Long houseId,boolean isActive){
		String hql = "from AccountDomain acc where acc.proRight.id=? and acc.house.id=? ";

		if (isActive) {
			hql += "and acc.isActive=1 ";
		}

		Query query = super.createQuery(hql,proRightId,houseId);
		List<AccountDomain> list = query.list();
		if (BlankUtil.isBlank(list)) {
			return null;
		}
		return list.get(0);
	}

	/**
	 * 功能描述:查询预交款帐户信息<br>
	 * @param searchVO
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-26<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	@AssociatedBuildingQuery(alias="H",secondAlias="H2", beginStr=" AND ", endStr=" ", insertAfterPoint="WHERE 1=1",secondInsertAfterPoint="WHERE 2=2",property4Building="BUILDING_ID",supportLoop=false)
	public Page queryPrePay(PrePayOffsetSearchVO searchVO) {
		List<Object> parameters=new ArrayList<Object>();
		SqlConfig sqlconfig=new SqlConfig("AccountSQL.xml");
		StringBuffer sql = new StringBuffer();
		sql.append(sqlconfig.getSql("queryPrepayCharge"));
		sql.append(buildSearchCondition(searchVO, parameters));
		sql.append(buildOrderBy(searchVO.getSortStr()));

		Page page=this.pagedSqlQueryByPageNo(sql.toString(), searchVO.getPageNo(), searchVO.getPageSize(), parameters.toArray());
		page.setResult(convertArrayTOVOList(page.getResult()));

		return page;
	}

	/**
	 * 功能描述:根据帐户ID查询预交帐户信息<br>
	 * @param accountId
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-7<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public PrePayOffsetVO findPrePayOffsetByAccountId(Long accountId){
		PrePayOffsetSearchVO searchVO=new PrePayOffsetSearchVO();
		searchVO.setAccountId(accountId);
		List<Object> parameters=new ArrayList<Object>();

		SqlConfig sqlconfig=new SqlConfig("AccountSQL.xml");
		StringBuffer sql = new StringBuffer();
		sql.append(sqlconfig.getSql("queryPrepayCharge"));

		sql.append(buildSearchCondition(searchVO, parameters));
		Query query=createSQLQuery(sql.toString(), parameters.toArray());
		query.setFirstResult(0);
		query.setMaxResults(1);
		Object[] result=(Object[])query.uniqueResult();
		return convertArrayTOVO(result);
	}

	/** 功能描述:<br>
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-10-7<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private StringBuffer getSearchSql() {
		StringBuffer  sql = new StringBuffer();
		sql.append("SELECT ACC.ACCOUNT_ID,");
		sql.append("        ACC.HOUSE_ID,");
		sql.append("        H.HOUSE_NUMBER,");
		sql.append("        VH.CLIENTS,");
		sql.append("        SCH.SCHEME_NAME,");
		sql.append("        PREPAYSUB.BALANCE as prePayBalance, ");
		sql.append("        CHARGESUB.BALANCE as chargeBalance,");
		sql.append("        PREPAYSUB.SUB_ACCOUNT_ID as prePayAccountId ");
		sql.append(" FROM   FEE_ACCOUNT ACC ");
		sql.append("        JOIN FEE_SUB_ACCOUNT PREPAYSUB ");
		sql.append("          ON (PREPAYSUB.ACCOUNT_ID = ACC.ACCOUNT_ID ");
		sql.append("              AND PREPAYSUB.ACCOUNT_TYPE = '002') ");
		sql.append("        JOIN FEE_SUB_ACCOUNT CHARGESUB ");
		sql.append("          ON (CHARGESUB.ACCOUNT_ID = ACC.ACCOUNT_ID ");
		sql.append("              AND CHARGESUB.ACCOUNT_TYPE = '001') ");
		sql.append("        JOIN HM_HOUSE H ");
		sql.append("          ON H.HOUSE_ID = ACC.HOUSE_ID ");
		sql.append("        LEFT JOIN HM_BUILDING HB ");
		sql.append("          ON H.BUILDING_ID = HB.BUILDING_ID ");
		sql.append("        LEFT JOIN CS_FLAT FL ");
		sql.append("          ON H.HOUSE_ID = FL.FLAT_ID ");
		sql.append("        JOIN FEE_PRE_ACCOUNT_SCH ASCH ");
		sql.append("          ON ASCH.PART_ACCOUNT_ID = PREPAYSUB.SUB_ACCOUNT_ID ");
		sql.append("        JOIN FEE_PRE_PAY_SCH SCH ");
		sql.append("          ON SCH.PRE_PAY_SCH_ID = ASCH.PRE_PAY_SCH_ID ");
		sql.append("        LEFT JOIN V_FEE_HOUSE_CLIENTS VH ");
		sql.append("          ON VH.HOUSE_ID = ACC.HOUSE_ID ");
		sql.append(" WHERE 1=1 ");
		return sql;
	}

	/**
	 * 功能描述:构建排序<br>
	 * @param orderStr
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-26<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private String buildOrderBy(String orderStr){
		if(null==orderStr || orderStr.trim().length()<=0){
			return " ORDER BY HH.SEQ_NO,HH.STOREY_NUMBER,HH.HOUSE_NUMBER ";
		}else if(orderStr.toUpperCase().indexOf("ASC")>=0){
			return " ORDER BY HH.SEQ_NO ASC,HH.STOREY_NUMBER ASC,HH.HOUSE_NUMBER ASC";
		}else if(orderStr.toUpperCase().indexOf("DESC")>=0){
			return " ORDER BY HH.SEQ_NO DESC,HH.STOREY_NUMBER DESC,HH.HOUSE_NUMBER DESC";
		}else{
			return "";
		}
	}
	/**
	 * 功能描述:查询所有有效帐户的余额。key 帐户id,value 帐户余额<br>
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-26<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public Map<Long,BigDecimal> getAllAccountBalance(){
		Map<Long,BigDecimal> result=new HashMap<Long, BigDecimal>();
		String hql="SELECT sub.account.accountId,sum(sub.balance) FROM AbstractSubAccount sub where account.isActive='1' GROUP BY sub";
		Query query=createQuery(hql, null);
		List<Object[]> list=query.list();
		for(Object[] item : list){
			result.put( (Long)item[0], (BigDecimal)item[1]);
		}
		return result;
	}

	/**
	 * 功能描述:将原生SQL查询出来的预交款对象数组列表转换为VO列表<br>
	 * @param arrayList
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-26<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private List<PrePayOffsetVO>  convertArrayTOVOList(List<Object[]> arrayList){
		List<PrePayOffsetVO> temp=new ArrayList<PrePayOffsetVO>();
		for(Object[] item : arrayList){
			temp.add(convertArrayTOVO(item));
		}
		return temp;
	}

	/**
	 * 功能描述:将原生SQL查询出来的预交款对象数组转换为VO<br>
	 * @param prePayA
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-26<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private PrePayOffsetVO convertArrayTOVO(Object[] prePayA){
		PrePayOffsetVO prePayOffsetVO=new PrePayOffsetVO();
		prePayOffsetVO.setAccountId( prePayA[0]==null ? null : ((BigInteger)prePayA[0]).longValue() ) ;
		prePayOffsetVO.setHouseId( prePayA[1]==null ? null : ((BigInteger)prePayA[1]).longValue() );
		prePayOffsetVO.setHouseName( prePayA[2]==null ? null : (String)prePayA[2] );
		prePayOffsetVO.setOwnerNames( prePayA[3]==null ? null : (String)prePayA[3] );
		prePayOffsetVO.setSchemeName( prePayA[4]==null ? null : (String)prePayA[4] );
		prePayOffsetVO.setPrePayAccountBalance( prePayA[5]==null ? null : (BigDecimal)prePayA[5] );
		prePayOffsetVO.setChargeAccountBalance( prePayA[6]==null ? null : (BigDecimal)prePayA[6] );
		prePayOffsetVO.setPrePayAccountId ( prePayA[7]==null ? null : ((BigInteger)prePayA[7]).longValue() ) ;
		return prePayOffsetVO;
	}
	/**
	 * 功能描述:构建预交款查询条件<br>
	 * @param searchVO
	 * @param parameters
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-26<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private String buildSearchCondition(PrePayOffsetSearchVO searchVO,List<Object> parameters){
		StringBuffer str=new StringBuffer();
		if(null!=searchVO && null!=searchVO.getBuildingId()){
			str.append(" AND HH.BUILDING_ID=? ");
			parameters.add(searchVO.getBuildingId());
		}
		if(null!=searchVO && null!=searchVO.getHouseId()){
			str.append(" AND HH.HOUSE_ID=? ");
			parameters.add(searchVO.getHouseId());
		}
		if(null!=searchVO && null!=searchVO.getPrePaySchId()){
			str.append(" AND HH.PRE_PAY_SCH_ID=? ");
			parameters.add(searchVO.getPrePaySchId());
		}
		if(null!=searchVO && null!=searchVO.getHouseName() && searchVO.getHouseName().trim().length()>0){
			str.append(" AND HH.HOUSE_NUMBER LIKE ? ");
			parameters.add("%"+searchVO.getHouseName()+"%");
		}
		if(null!=searchVO && null!=searchVO.getOwnerNames() && searchVO.getOwnerNames().trim().length()>0){
			str.append(" AND HH.CLIENTS LIKE ? ");
			parameters.add("%"+searchVO.getOwnerNames()+"%");
		}
		if(null!=searchVO && null!=searchVO.getAccountId()){
			str.append(" AND HH.ACCOUNT_ID=? ");
			parameters.add(searchVO.getAccountId());
		}else{
			str.append(" AND HH.IS_ACTIVE = '1' ");
		}

		return str.toString();
	}
}
